import sqlite3
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import os

# 创建图表输出目录
if not os.path.exists('charts'):
    os.makedirs('charts')

# 创建数据库连接
conn = sqlite3.connect('insurance_data.db')
cursor = conn.cursor()

# 设置中文字体支持
plt.rcParams['font.sans-serif'] = ['SimHei', 'Arial Unicode MS', 'Microsoft YaHei', 'DejaVu Sans']
plt.rcParams['axes.unicode_minus'] = False


# 1. 创建业务表结构
def create_tables():
    cursor.executescript('''
                         -- 保险合同表
                         CREATE TABLE IF NOT EXISTS contracts
                         (
                             contract_id
                             INTEGER
                             PRIMARY
                             KEY,
                             region
                             TEXT
                             NOT
                             NULL,
                             is_chief_reinsurer
                             INTEGER
                             DEFAULT
                             0, -- 1:首席再保人 0:非首席
                             business_type
                             TEXT
                             CHECK (
                             business_type
                             IN
                         (
                             '再保财险',
                             '再保人身险',
                             '人身险'
                         )),
                             sign_date DATE NOT NULL,
                             premium_amount REAL NOT NULL
                             );

                         -- 保费明细表
                         CREATE TABLE IF NOT EXISTS premiums
                         (
                             premium_id
                             INTEGER
                             PRIMARY
                             KEY,
                             contract_id
                             INTEGER
                             REFERENCES
                             contracts
                         (
                             contract_id
                         ),
                             period_type TEXT CHECK
                         (
                             period_type
                             IN
                         (
                             '长期',
                             '短期'
                         )), -- 长/短期险
                             channel_type TEXT CHECK
                         (
                             channel_type
                             IN
                         (
                             '团体',
                             '个人'
                         )), -- 团/个险
                             payment_type TEXT CHECK
                         (
                             payment_type
                             IN
                         (
                             '期缴',
                             '趸缴'
                         )),
                             policy_year INTEGER, -- 保单年度
                             is_new_policy INTEGER DEFAULT 0 -- 是否新单
                             );

                         -- 资产表
                         CREATE TABLE IF NOT EXISTS assets
                         (
                             record_date
                             DATE
                             NOT
                             NULL,
                             region
                             TEXT
                             NOT
                             NULL,
                             total_assets
                             REAL
                             NOT
                             NULL,
                             PRIMARY
                             KEY
                         (
                             record_date,
                             region
                         )
                             );

                         -- 退保记录表
                         CREATE TABLE IF NOT EXISTS surrenders
                         (
                             surrender_id
                             INTEGER
                             PRIMARY
                             KEY,
                             contract_id
                             INTEGER
                             REFERENCES
                             contracts
                         (
                             contract_id
                         ),
                             surrender_amount REAL NOT NULL,
                             surrender_date DATE NOT NULL,
                             is_hesitation_period INTEGER DEFAULT 1 -- 是否犹豫期
                             );

                         -- 业务价值表
                         CREATE TABLE IF NOT EXISTS business_values
                         (
                             value_id
                             INTEGER
                             PRIMARY
                             KEY,
                             region
                             TEXT
                             NOT
                             NULL,
                             record_date
                             DATE
                             NOT
                             NULL,
                             new_business_value
                             REAL
                             NOT
                             NULL, -- 新单业务价值
                             effective_business_value
                             REAL
                             NOT
                             NULL  -- 有效业务价值
                         );

                         -- 保费预估表
                         CREATE TABLE IF NOT EXISTS premium_estimates
                         (
                             estimate_id
                             INTEGER
                             PRIMARY
                             KEY,
                             region
                             TEXT
                             NOT
                             NULL,
                             business_year
                             INTEGER
                             NOT
                             NULL,
                             estimated_premium
                             REAL
                             NOT
                             NULL, -- 预估分保费
                             actual_premium
                             REAL
                             NOT
                             NULL  -- 实际分保费
                         );
                         ''')
    conn.commit()


# 2. 生成模拟数据
def generate_test_data():
    # 删除表结构
    cursor.executescript('''
                         DROP TABLE IF EXISTS assets;
                         DROP TABLE IF EXISTS contracts;
                         DROP TABLE IF EXISTS premiums;
                         DROP TABLE IF EXISTS surrenders;
                         DROP TABLE IF EXISTS business_values;
                         DROP TABLE IF EXISTS premium_estimates;
                         ''')

    # 重新创建表结构
    create_tables()

    # 生成保险合同数据 (1000条)
    regions = ['华东', '华北', '华南', '华中', '西部']
    business_types = ['再保财险', '再保人身险', '人身险']

    contracts_data = []
    for i in range(1, 1001):
        region = np.random.choice(regions)
        business_type = np.random.choice(business_types)

        # 修正：先计算日期，再格式化为字符串
        sign_date = datetime(2025, 1, 1) + timedelta(days=np.random.randint(0, 365))
        sign_date_str = sign_date.strftime('%Y-%m-%d')

        contracts_data.append((
            i,  # contract_id
            region,
            np.random.choice([0, 1]),  # is_chief_reinsurer
            business_type,
            sign_date_str,
            round(np.random.uniform(10000, 500000), 2)  # premium_amount
        ))

    cursor.executemany(
        "INSERT INTO contracts VALUES (?, ?, ?, ?, ?, ?)",
        contracts_data
    )

    # 生成保费明细数据
    premiums_data = []
    for contract_id in range(1, 1001):
        premiums_data.append((
            contract_id,  # premium_id 使用contract_id关联
            contract_id,
            np.random.choice(['长期', '短期']),
            np.random.choice(['团体', '个人']),
            np.random.choice(['期缴', '趸缴']),
            np.random.randint(1, 20),
            np.random.choice([0, 1])
        ))

    cursor.executemany(
        "INSERT INTO premiums VALUES (?, ?, ?, ?, ?, ?, ?)",
        premiums_data
    )

    # 生成资产数据 (每月一条)
    assets_data = []
    for i in range(12):
        for region in regions:
            # 修正：确保日期格式正确
            record_date = f'2025-{i + 1:02d}-01'
            assets_data.append((
                record_date,
                region,
                round(np.random.uniform(1e8, 5e9), 2)
            ))

    cursor.executemany(
        "INSERT INTO assets VALUES (?, ?, ?)",
        assets_data
    )

    # 生成退保数据 (模拟一些退保记录)
    surrenders_data = []
    for i in range(1, 101):  # 100条退保记录
        contract_id = np.random.randint(1, 1000)
        surrender_date = (datetime(2025, 1, 1) + timedelta(days=np.random.randint(0, 365))).strftime('%Y-%m-%d')
        surrenders_data.append((
            i,
            contract_id,
            round(np.random.uniform(1000, 50000), 2),
            surrender_date,
            1  # 犹豫期退保
        ))

    cursor.executemany(
        "INSERT INTO surrenders VALUES (?, ?, ?, ?, ?)",
        surrenders_data
    )

    # 生成业务价值数据 (季度)
    business_values_data = []
    value_id_counter = 1  # 唯一ID计数器
    for q in range(1, 5):
        for region in regions:
            business_values_data.append((
                value_id_counter,  # 使用唯一ID
                region,
                f'2025-{q * 3:02d}-01',
                round(np.random.uniform(5e6, 2e7), 2),
                round(np.random.uniform(1e8, 5e8), 2)
            ))
            value_id_counter += 1  # 递增ID

    cursor.executemany(
        "INSERT INTO business_values VALUES (?, ?, ?, ?, ?)",
        business_values_data
    )

    # 生成保费预估数据
    premium_estimates_data = []
    for i in range(1, 51):  # 50条预估数据
        region = np.random.choice(regions)
        premium_estimates_data.append((
            i,
            region,
            2025,
            round(np.random.uniform(1e6, 1e7), 2),
            round(np.random.uniform(1e6, 1e7), 2)
        ))

    cursor.executemany(
        "INSERT INTO premium_estimates VALUES (?, ?, ?, ?, ?)",
        premium_estimates_data
    )

    conn.commit()
    print("测试数据生成完成：1000条合同，1000条保费，60条资产，100条退保，20条业务价值，50条保费预估")


# 3. 业务指标计算函数
def calculate_metrics(region=None):
    """计算所有业务指标并按区域过滤"""
    # 构建基础 WHERE 子句
    base_where = "WHERE 1=1"  # 永远为真的条件，便于后续添加 AND
    if region:
        base_where += f" AND region = '{region}'"

    # 指标1: 首席再保人合同占比
    cursor.execute(f"""
    SELECT 
        ROUND(SUM(CASE WHEN is_chief_reinsurer = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS chief_ratio
    FROM contracts 
    {base_where} AND business_type IN ('再保财险', '再保人身险')
    """)
    chief_ratio = cursor.fetchone()[0] or 0

    # 指标2: 首席再保人保费占比
    cursor.execute(f"""
    SELECT 
        ROUND(SUM(CASE WHEN is_chief_reinsurer = 1 THEN premium_amount ELSE 0 END) * 100.0 / 
        SUM(premium_amount), 2) AS chief_premium_ratio
    FROM contracts 
    {base_where} AND business_type IN ('再保财险', '再保人身险')
    """)
    chief_premium_ratio = cursor.fetchone()[0] or 0

    # 指标8: 犹豫期保费退保率
    cursor.execute(f"""
    SELECT 
        ROUND(COALESCE(SUM(s.surrender_amount), 0) * 100.0 / 
        (COALESCE(SUM(p.premium_amount), 0) + COALESCE(SUM(s.surrender_amount), 0)), 2) AS surrender_rate
    FROM contracts p
    LEFT JOIN surrenders s ON p.contract_id = s.contract_id 
        AND s.is_hesitation_period = 1
        AND strftime('%Y', s.surrender_date) = '2025'
    {base_where.replace("1=1", "p.region = p.region")}  -- 使用相同的技巧
    """)
    surrender_rate = cursor.fetchone()[0] or 0

    # 指标10: 资产增量保费比
    # 构建子查询中的区域条件
    region_condition = f"AND region = '{region}'" if region else ""

    cursor.execute(f"""
    SELECT 
        ROUND((MAX(a2.total_assets) - MIN(a1.total_assets)) * 100.0 / 
        (SELECT SUM(premium_amount) FROM contracts 
         WHERE strftime('%Y', sign_date) = '2025' {region_condition}), 2) AS asset_premium_ratio
    FROM (
        SELECT region, total_assets FROM assets 
        WHERE record_date = '2025-01-01' {region_condition}
    ) a1
    JOIN (
        SELECT region, total_assets FROM assets 
        WHERE record_date = '2025-12-01' {region_condition}
    ) a2 ON a1.region = a2.region
    """)
    asset_premium_ratio = cursor.fetchone()[0] or 0

    # 指标11: 保费预估差异率
    cursor.execute(f"""
    SELECT 
        ROUND((SUM(estimated_premium) - SUM(actual_premium)) * 100.0 / SUM(actual_premium), 2) AS premium_diff_ratio
    FROM premium_estimates
    {base_where} AND business_year = 2025
    """)
    premium_diff_ratio = cursor.fetchone()[0] or 0

    return {
        '首席再保人合同占比(%)': chief_ratio,
        '首席再保人保费占比(%)': chief_premium_ratio,
        '犹豫期退保率(%)': surrender_rate,
        '资产增量保费比(%)': asset_premium_ratio,
        '保费预估差异率(%)': premium_diff_ratio
    }


# 4. 按时间粒度统计
def time_based_metrics(time_period='month'):
    """按时间粒度统计指标"""
    if time_period == 'month':
        period_format = "%Y-%m"
    elif time_period == 'year':
        period_format = "%Y"
    else:  # week
        period_format = "%Y-%W"

    # 按月/年/周统计保费
    cursor.execute(f"""
    SELECT 
        strftime('{period_format}', sign_date) AS period,
        SUM(premium_amount) AS total_premium
    FROM contracts
    GROUP BY period
    ORDER BY period
    """)
    return cursor.fetchall()


# 5. 导出结果到表格
def export_results():
    # 计算所有区域指标
    regions = ['华东', '华北', '华南', '华中', '西部']
    all_metrics = []
    for region in regions:
        metrics = calculate_metrics(region)
        metrics['区域'] = region
        all_metrics.append(metrics)

    # 转换为DataFrame并导出
    df_metrics = pd.DataFrame(all_metrics)

    # 添加全国汇总
    national_metrics = calculate_metrics()
    national_metrics['区域'] = '全国'
    df_metrics = pd.concat([df_metrics, pd.DataFrame([national_metrics])], ignore_index=True)

    # 时间维度统计
    weekly_data = time_based_metrics('week')
    monthly_data = time_based_metrics('month')
    yearly_data = time_based_metrics('year')

    df_weekly = pd.DataFrame(weekly_data, columns=['周', '总保费'])
    df_monthly = pd.DataFrame(monthly_data, columns=['月', '总保费'])
    df_yearly = pd.DataFrame(yearly_data, columns=['年', '总保费'])

    # 导出到Excel的不同工作表
    with pd.ExcelWriter('保险业务指标.xlsx') as writer:
        df_metrics.to_excel(writer, sheet_name='区域指标', index=False)
        df_weekly.to_excel(writer, sheet_name='周统计', index=False)
        df_monthly.to_excel(writer, sheet_name='月统计', index=False)
        df_yearly.to_excel(writer, sheet_name='年统计', index=False)

    print("结果已导出到: 保险业务指标.xlsx")

    # 生成并导出图表
    export_charts(df_metrics, df_weekly, df_monthly, df_yearly)

    return df_metrics, df_weekly, df_monthly, df_yearly


# 6. 图表生成与导出
def export_charts(df_metrics, df_weekly, df_monthly, df_yearly):
    """为每个指标生成图表并导出为图片"""
    # 区域指标图表
    regions = df_metrics['区域'].tolist()

    # 设置图表风格
    plt.style.use('ggplot')

    # 1. 首席再保人合同占比
    plt.figure(figsize=(12, 6))
    plt.bar(regions, df_metrics['首席再保人合同占比(%)'], color='#4C72B0')
    plt.title('首席再保人合同占比 (%)')
    plt.ylabel('占比 (%)')
    plt.ylim(0, 100)
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/首席再保人合同占比.png')
    plt.close()

    # 2. 首席再保人保费占比
    plt.figure(figsize=(12, 6))
    plt.bar(regions, df_metrics['首席再保人保费占比(%)'], color='#55A868')
    plt.title('首席再保人保费占比 (%)')
    plt.ylabel('占比 (%)')
    plt.ylim(0, 100)
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/首席再保人保费占比.png')
    plt.close()

    # 3. 犹豫期退保率
    plt.figure(figsize=(12, 6))
    plt.bar(regions, df_metrics['犹豫期退保率(%)'], color='#C44E52')
    plt.title('犹豫期退保率 (%)')
    plt.ylabel('退保率 (%)')
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/犹豫期退保率.png')
    plt.close()

    # 4. 资产增量保费比
    plt.figure(figsize=(12, 6))
    plt.bar(regions, df_metrics['资产增量保费比(%)'], color='#8172B2')
    plt.title('资产增量保费比 (%)')
    plt.ylabel('比率 (%)')
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/资产增量保费比.png')
    plt.close()

    # 5. 保费预估差异率
    plt.figure(figsize=(12, 6))
    plt.bar(regions, df_metrics['保费预估差异率(%)'], color='#CCB974')
    plt.title('保费预估差异率 (%)')
    plt.ylabel('差异率 (%)')
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/保费预估差异率.png')
    plt.close()

    # 时间维度图表
    # 周统计
    plt.figure(figsize=(14, 7))
    plt.plot(df_weekly['周'], df_weekly['总保费'], marker='o', linestyle='-', color='#4C72B0')
    plt.title('每周保费总额趋势')
    plt.xlabel('周')
    plt.ylabel('总保费')
    plt.xticks(rotation=45)
    plt.grid(True, linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/每周保费趋势.png')
    plt.close()

    # 月统计
    plt.figure(figsize=(14, 7))
    plt.plot(df_monthly['月'], df_monthly['总保费'], marker='o', linestyle='-', color='#55A868')
    plt.title('每月保费总额趋势')
    plt.xlabel('月')
    plt.ylabel('总保费')
    plt.xticks(rotation=45)
    plt.grid(True, linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/每月保费趋势.png')
    plt.close()

    # 年统计
    plt.figure(figsize=(10, 6))
    plt.bar(df_yearly['年'], df_yearly['总保费'], color='#C44E52')
    plt.title('年度保费总额')
    plt.xlabel('年')
    plt.ylabel('总保费')
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('charts/年度保费总额.png')
    plt.close()

    print(f"图表已导出到 charts/ 目录")


# 7. 测试函数
def run_tests():
    print("=== 开始测试 ===")
    create_tables()
    generate_test_data()

    # 测试指标计算
    print("\n测试指标计算(华东区):")
    print(calculate_metrics('华东'))

    # 测试全国指标
    print("\n测试全国指标:")
    print(calculate_metrics())

    # 导出结果
    export_results()
    print("\n=== 测试完成 ===")


# 8. 主程序
if __name__ == "__main__":
    run_tests()
    conn.close()